package dbschema

import (
	"git.sr.ht/~mil/mobsql/sqlgen"
)

var tablesGTFS = []sqlgen.LoadTable{
	sqlgen.LoadTable{
		File: "agency.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "agency",
			UniqueSet: []string{"source", "agency_id"},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "agency_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "agency_name", DType: sqlgen.DTypeText, Required: true},
				{Column: "agency_url", DType: sqlgen.DTypeText, Required: true},
				{Column: "agency_timezone", DType: sqlgen.DTypeText, Required: true},
			},
		},
	},
	sqlgen.LoadTable{
		File: "calendar.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "calendar",
			Optional:  true,
			UniqueSet: []string{"source", "service_id"},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "service_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "monday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "tuesday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "wednesday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "thursday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "friday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "saturday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "sunday", DType: sqlgen.DTypeInt, Required: true, Enum: []int{0, 1}},
				{Column: "start_date", DType: sqlgen.DTypeInt, Required: true},
				{Column: "end_date", DType: sqlgen.DTypeInt, Required: true},
			},
		},
	},
	sqlgen.LoadTable{
		File: "calendar_dates.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "calendar_dates",
			Optional:  true,
			UniqueSet: []string{},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "service_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "date", DType: sqlgen.DTypeInt, Required: true},
				{Column: "exception_type", DType: sqlgen.DTypeInt, Required: true, Enum: []int{1, 2}},
			},
		},
	},
	sqlgen.LoadTable{
		File: "stop_times.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "stop_times",
			UniqueSet: []string{"source", "trip_id", "stop_sequence"},
			Indexes:   [][]string{{"departure_time"}, {"trip_id"}},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "trip_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "arrival_time", DType: sqlgen.DTypeInt, Conversion: sqlgen.ColonTimeToInt},
				{Column: "departure_time", DType: sqlgen.DTypeInt, Conversion: sqlgen.ColonTimeToInt},
				{Column: "stop_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "stop_sequence", DType: sqlgen.DTypeInt, Required: true},
				{Column: "stop_headsign", DType: sqlgen.DTypeText},
				{Column: "pickup_type", DType: sqlgen.DTypeInt},
				{Column: "drop_off_type", DType: sqlgen.DTypeInt},
				{Column: "continuous_drop_off", DType: sqlgen.DTypeText},
				{Column: "timepoint", DType: sqlgen.DTypeInt, Enum: []int{0, 1}},
			},
		},
	},
	sqlgen.LoadTable{
		File: "stops.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "stops",
			UniqueSet: []string{"source", "stop_id"},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "stop_id", DType: sqlgen.DTypeID, Required: true},
				{Column: "stop_code", DType: sqlgen.DTypeText},
				{
					Column: "stop_name",
					DType:  sqlgen.DTypeText,
					Check: `
						((location_type is null or location_type = 0 or location_type = 1 or location_type = 2) and stop_name is not null) or
						(location_type = 3 or location_type = 4)
					`,
				},
				{Column: "stop_desc", DType: sqlgen.DTypeText},
				{
					Column: "stop_lat",
					DType:  sqlgen.DTypeReal,
					Check: `
						((location_type is null or location_type = 0 or location_type = 1 or location_type = 2) and stop_lat is not null) or
						(location_type = 3 or location_type = 4)
					`,
				},
				{
					Column: "stop_lon",
					DType:  sqlgen.DTypeReal,
					Check: `
						((location_type is null or location_type = 0 or location_type = 1 or location_type = 2) and stop_lon is not null) or
						(location_type = 3 or location_type = 4)
					`,
				},
				{Column: "zone_id", DType: sqlgen.DTypeID},
				{Column: "stop_url", DType: sqlgen.DTypeText},
				{Column: "location_type", DType: sqlgen.DTypeInt, Enum: []int{0, 1, 2, 3, 4}},
				{Column: "parent_station", DType: sqlgen.DTypeID},
				{Column: "stop_timezone", DType: sqlgen.DTypeText},
				{Column: "wheelchair_boarding", DType: sqlgen.DTypeInt, Enum: []int{0, 1, 2}},
				{Column: "level_id", DType: sqlgen.DTypeID},
				{Column: "platform_code", DType: sqlgen.DTypeText},
			},
		},
	},
	sqlgen.LoadTable{
		File: "trips.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "trips",
			UniqueSet: []string{"source", "trip_id"},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "route_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "service_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "trip_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "trip_headsign", DType: sqlgen.DTypeText},
				{Column: "trip_short_name", DType: sqlgen.DTypeText},
				{Column: "direction_id", DType: sqlgen.DTypeInt, Enum: []int{0, 1}},
				{Column: "block_id", DType: sqlgen.DTypeText},
				{Column: "shape_id", DType: sqlgen.DTypeText},
				{Column: "wheelchair_accessible", DType: sqlgen.DTypeText, Enum: []int{0, 1, 2}},
				{Column: "bikes_allowed", DType: sqlgen.DTypeInt, Enum: []int{0, 1, 2}},
			},
		},
	},
	sqlgen.LoadTable{
		File: "routes.txt",
		TableSpec: sqlgen.TableSpec{
			Table:     "routes",
			UniqueSet: []string{"source", "route_id"},
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "route_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "agency_id", DType: sqlgen.DTypeText},
				{
					Column: "route_short_name",
					DType:  sqlgen.DTypeText,
					Check:  `route_long_name is not null or route_short_name is not null`,
				},
				{Column: "route_long_name", DType: sqlgen.DTypeText},
				{Column: "route_desc", DType: sqlgen.DTypeText},
				{Column: "route_type", DType: sqlgen.DTypeInt},
				{Column: "route_url", DType: sqlgen.DTypeText},
				{Column: "route_color", DType: sqlgen.DTypeText},
				{Column: "route_text_color", DType: sqlgen.DTypeText},
				{Column: "route_sort_order", DType: sqlgen.DTypeText},
				{Column: "continuous_pickup", DType: sqlgen.DTypeInt, Enum: []int{0, 1, 2, 3}},
				{Column: "continuous_drop_off", DType: sqlgen.DTypeInt, Enum: []int{0, 1, 2, 3}},
			},
		},
	},
	sqlgen.LoadTable{
		File: "transfers.txt",
		TableSpec: sqlgen.TableSpec{
			Table:    "transfers",
			Optional: true,
			Columns: []sqlgen.LoadColumn{
				{Column: "source", DType: sqlgen.DTypeInt, Required: true},
				{Column: "from_stop_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "to_stop_id", DType: sqlgen.DTypeText, Required: true},
				{Column: "transfer_type", DType: sqlgen.DTypeInt},
				{Column: "min_transfer_time", DType: sqlgen.DTypeInt},
			},
		},
	},
}
